package com.es.hr.emp;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import com.es.util.common.CommonBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class EmpBiz {

	public ArrayList<CommonBean> getAreaList(String type)throws Exception {

		ArrayList<String> query = new ArrayList<String>();
		ArrayList<CommonBean> result = new ArrayList<CommonBean>();
		query.add(type);
		Dao dao = new Dao();
		String sql = "select common_value,common_code from hrm_common where common_type=? ";
		try {
			ResultSet resultSet = dao.Select(sql,query);
			while(resultSet.next()){
				CommonBean bean = new CommonBean();
				bean.setCommonCode(resultSet.getString("common_code"));
				bean.setCommonCode(resultSet.getString("common_value"));
				result.add(bean);
			}
		} finally{
			dao.close();
		}
		return result;
	}
	
	public PageBean queryCtmListPop(PageNavigator pageNav, ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		PageBean pageBean = new  PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select emp.emp_shiyebu,emp.emp_id,emp.emp_area_value,emp.emp_province_value,emp.emp_city_value,emp.emp_name,emp.emp_login_id,(select top 1 common_value from hrm_common " +
					"where common_code = emp.emp_role and common_type='role') emp_role,emp.emp_code from hrm_emp_info emp " +
					"where emp.del_flag='N' " ;
			String[] sqlArrary	= new String[] {"and emp.emp_area_code=? ",
						"and emp.emp_province_code=? ",
						"and emp.emp_city_code=? ",
						"and UPPER(emp.emp_login_id) like '%'+UPPER(?)+'%' ",
						"and emp.emp_role=? ",
						"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
						"and emp.emp_shiyebu=? "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			int total = dao.getAllRow(sql,query);		
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total%pageNav.getPageRow()==0?total/pageNav.getPageRow():total/pageNav.getPageRow()+1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally{
			dao.close();
		}
		return pageBean;
	}
	
	public PageBean queryShopListPop(PageNavigator pageNav, ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		PageBean pageBean = new  PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select shop.shop_id,shop.shop_code,shop.shop_name,shop.shop_level,shop.shop_area_value,shop.shop_province_value,shop.shop_city_value,shop.shop_citys_value, "
				+ " (select top 1 a.emp_name from hrm_emp_info a where shop.shop_ctm_code = a.emp_id)  shop_ctm_name,shop.shop_ctm_code ,shop.shop_position_value " 
				+ "from hrm_emp_info emp,hrm_shop_info shop "
				+ " where shop.shop_city_code = emp.emp_city_code " ;
			String[] sqlArrary = new String[] { "and shop.shop_area_code=? ",
					"and shop.shop_province_code=? ", "and shop.shop_city_code=? ",
					"and UPPER(shop.shop_code) like '%'+UPPER(?)+'%'",
					"and UPPER(shop.shop_name) like '%'+UPPER(?)+'%'"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")) {
					sql += sqlArrary[i-1];
					query.add(param.get(i).trim());
				}
			}
			int total = dao.getAllRow(sql,query);		
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total%pageNav.getPageRow()==0?total/pageNav.getPageRow():total/pageNav.getPageRow()+1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally{
			dao.close();
		}
		return pageBean;
	}
	
	public boolean CreateEmpInfoDo(EmpInfoBean bean) throws Exception{
		Dao dao = new Dao();
		ArrayList<String> query = new ArrayList<String>();
		String extraField = "emp_id,emp_info_bakup,emp_lizhi_yuanyin,emp_lizhidan_yn,emp_lizhi_date";
		StringBuffer sb = new StringBuffer();
		StringBuffer sb1 = new StringBuffer();
		Field[] fds = Class.forName("com.es.hr.emp.EmpInfoBean").getDeclaredFields();   
		 
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals("emp_login_id")){
				sb.append(fds[i].getName()+",");
				sb1.append("?,");
				query.add(bean.getAiwang());
			}else if(fds[i].getName().equals("emp_login_yn")/*&&bean.getEmp_role().equals(anObject)*/){
				sb.append(fds[i].getName()+",");
				sb1.append("?,");
				query.add("Y");
			}else if(fds[i].getName().equals("emp_city_level")){

			}else if(extraField.indexOf(fds[i].getName())<0){
				sb.append(fds[i].getName()+",");
				sb1.append("?,");
				query.add((String)fds[i].get(bean));
			} 	  
		}		
		try {
			String sql = "insert into hrm_emp_info("+ sb.toString().substring(0, sb.toString().length()-1)+") values("+
				sb1.toString().substring(0, sb1.toString().length()-1)+" )";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
	
	public EmpInfoBean getEmpInfoById(String emp_id)throws Exception {

		EmpInfoBean bean = new EmpInfoBean();
		if(emp_id==null) return bean;
		
		ArrayList<String> query = new ArrayList<String>();
		query.add(emp_id);
		Dao dao = new Dao();
		String sql = "select top 1 * ,(select top 1 a.emp_name from hrm_emp_info a where a.emp_id = emp.emp_ctm_code)emp_ctm_name  from  hrm_emp_info emp where emp_id = ? ";
		try {
			ResultSet resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData(); 
			if(resultSet.next()){
				  for(int i = 0;i<rss.getColumnCount(); i++){ 	
					  if(rss.getColumnName(i+1).equals("emp_id"))
						  bean.setEmp_id(resultSet.getLong("emp_id"));
					  else if(rss.getColumnName(i+1).equals("emp_sex"))
						  bean.setEmp_sex(resultSet.getString("emp_sex"));
					  else if(!rss.getColumnName(i+1).equals("emp_lizhi_date"))
						  EmpInfoBeanSet(bean,rss.getColumnName(i+1),(String) resultSet.getObject(rss.getColumnName(i+1)));
				 }	
			  }
		} finally{
			dao.close();
		}
		return bean;
	}
	
	private void EmpInfoBeanSet(EmpInfoBean bean,String field,String value) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException{
		Field[] fds = Class.forName("com.es.hr.emp.EmpInfoBean").getDeclaredFields();   
		
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals(field)){
				fds[i].set(bean, value);
				return;
			}
		}
	}
}
